/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/03/15
 */
package vn.smartware.co.server.dao.business;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.commons.Const;
import vn.smartware.co.dto.BillDTO;
import vn.smartware.co.dto.MstCustomerDTO;
import vn.smartware.co.dto.MstRoomDTO;
import vn.smartware.co.dto.ReserveDetailDTO;
import vn.smartware.co.dto.ReserveDto;
import vn.smartware.co.forms.ReserveForm;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.dao.factory.AbstractReserveDAO;
import vn.smartware.co.server.sql.factory.AbstractRegisterRoomSQLManager;
import vn.smartware.co.server.sql.factory.AbstractReserveSQLManager;
import vn.smartware.co.server.sql.factory.SQLFactory;

/**
 * @author thuctt
 * ReserveDAO
 */
@SuppressWarnings({"rawtypes"})
public class ReserveDAO extends AbstractReserveDAO {

	private static Log log = LogFactory.getLog(ReserveDAO.class);
	
	/**
	 * getReserveInfo
	 * 
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void getReserveInfo(ReserveForm form, LoginDTO user, Connection conn)
			throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		ResultSet rs = null;
		int count = 0;
		try {
			MstRoomDTO room = form.getReserveDto().getRoom();
			manager.makeGetReserveInfoSQL(room, user, conn);
			rs = manager.executeQuery();
			
			while (rs.next()){
				ReserveDto dto = form.getReserveDto();
				dto.setReserveId(rs.getString("RESERVE_ID"));
				dto.setRentType(rs.getString("RENT_TYPE"));
				dto.setRentTypeName(rs.getString("RENT_TYPE_NAME"));
				dto.setBookingYmd(rs.getString("BOOKING_YMD"));
				dto.setBookingTime(rs.getString("BOOKING_TIME"));
				dto.setInuseYmd(rs.getString("INUSE_YMD"));
				dto.setInuseTime(rs.getString("INUSE_TIME"));
				dto.setFinishYmd(rs.getString("FINISH_YMD"));
				dto.setFinishTime(rs.getString("FINISH_TIME"));
				dto.setNote(rs.getString("NOTE"));
				dto.setCurrency(rs.getString("CURRENCY"));
				
				room.setRoomNo(rs.getString("ROOM_NO"));
				room.setRoomName(rs.getString("ROOM_NAME"));
				room.setRoomType(rs.getString("ROOM_TYPE"));
				room.setRoomTypeName(rs.getString("ROOM_TYPE_NAME"));
				room.setStatus(rs.getString("ROOM_STATUS"));
				room.setStatusName(rs.getString("ROOM_STATUS_NAME"));
				dto.setRoom(room);
				
				MstCustomerDTO cus = new MstCustomerDTO();
				cus.setCustomerNo(rs.getString("CUSTOMER_NO"));
				cus.setName(rs.getString("CUSTOMER_NAME"));
				cus.setIdNumber(rs.getString("ID_NUMBER"));
				cus.setTel(rs.getString("TEL"));
				dto.setCustomer(cus);
				
				if (!Utils.isNullEmpty(dto.getFinishYmd())
						&& !Utils.isNullEmpty(dto.getFinishTime())){
					BillDTO bill = new BillDTO();
					bill.setStartTime(rs.getString("START_TIME"));
					bill.setStartYmd(rs.getString("START_YMD"));
					bill.setEndYmd(rs.getString("END_YMD"));
					bill.setEndTime(rs.getString("END_TIME"));
					bill.setPayMoney(rs.getString("PAY_MONEY"));
					
					dto.setBill(bill);
				}
				
				count++;
			}
			
			log.info(count + " records.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * getDetailDrink
	 * @param reserveId
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getDetailDrink(String reserveId, Connection conn)
			throws ServerException {
		
		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList list = new ArrayList();
		try {
			
			manager.makeGetDetailDrinkSQL(reserveId, conn);
			rs = manager.executeQuery();
			
			while (rs.next()){
				ReserveDetailDTO det = new ReserveDetailDTO();
				det.setDetailNo(rs.getString("DRINK_NO"));
				det.setDetailname(rs.getString("NAME"));
				det.setQuantity(rs.getString("QTY"));
				det.setPrice(rs.getString("PRICE"));
				det.setUnitPrice(rs.getString("UNIT_PRICE"));
				
				list.add(det);
				count++;
			}
			
			log.info(count + " records.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
		return list;
	}

	/**
	 * getDetailFood
	 * @param reserveId
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getDetailFood(String reserveId, Connection conn)
			throws ServerException {
		
		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList list = new ArrayList();
		try {
			
			manager.makeGetDetailFoodSQL(reserveId, conn);
			rs = manager.executeQuery();
			
			while (rs.next()){
				ReserveDetailDTO det = new ReserveDetailDTO();
				det.setDetailNo(rs.getString("FOOD_NO"));
				det.setDetailname(rs.getString("NAME"));
				det.setQuantity(rs.getString("QTY"));
				det.setPrice(rs.getString("PRICE"));
				det.setUnitPrice(rs.getString("UNIT_PRICE"));
				
				list.add(det);
				count++;
			}
			
			log.info(count + " records.");
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
		return list;
	}

	/**
	 * create new reservation
	 * @param form
	 * @param user
	 * @param conn
	 * @return String
	 * @throws ServerException
	 */
	public String createReservation(ReserveForm form, LoginDTO user,
			Connection conn) throws ServerException {
		
		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		AbstractRegisterRoomSQLManager roomMng = SQLFactory.createRegisterRoomSQLManager();
		ResultSet rs = null;
		int count = 0;
		String reserveId = "";
		try {
			manager.makeCreateReservationSQL(form, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");
			// get reserve no just registered
			manager = SQLFactory.createReserveSQLManager();
			manager.getMaxReserveNoSQL(conn);
			rs = manager.executeQuery();
			if (rs.next()) {
				reserveId = rs.getString("RESERVE_ID");
			}
			//update room status
			MstRoomDTO room = form.getReserveDto().getRoom();
			roomMng.makeUpdateRoomStatus(room.getRoomNo(), form.getReserveDto().getReserveStatus(), user, conn);
			count = roomMng.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				roomMng.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
		return reserveId;
	}

	/**
	 * registerDetailDrink
	 * @param reserveId
	 * @param drink
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void registerDetailDrink(String reserveId, ReserveDetailDTO drink,
			LoginDTO user, Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeRegisterDetailDrinkSQL(reserveId, drink, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * registerDetailFood
	 * @param reserveId
	 * @param food
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void registerDetailFood(String reserveId, ReserveDetailDTO food,
			LoginDTO user, Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeRegisterDetailFoodSQL(reserveId, food, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * delete detail food/drink
	 * @param reserveId
	 * @param detail
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void deleteDetail(String reserveId, int detail,
			Connection conn)
			throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeDeleteDetailSQL(reserveId, detail, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * deleteReservation
	 * @param form
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void deleteReservation(ReserveForm form, LoginDTO user,
			Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		AbstractRegisterRoomSQLManager roomMng = SQLFactory.createRegisterRoomSQLManager();
		int count = 0;
		try {
			manager.makeDeleteReservationSQL(form.getReserveDto().getReserveId(), conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");
			
			//update room status
			MstRoomDTO room = form.getReserveDto().getRoom();
			roomMng.makeResetRoomStatus(room.getRoomNo(), user, conn);
			count = roomMng.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * updateReservation
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void updateReservation(ReserveForm form, LoginDTO user,
			Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		AbstractRegisterRoomSQLManager roomMng = SQLFactory.createRegisterRoomSQLManager();
		int count = 0;
		boolean updRoomState = false;
		try {
			manager.makeUpdateReservationSQL(form, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");
			
			//update room status
			MstRoomDTO room = form.getReserveDto().getRoom();
			String reserveStatus = null;
			if (!Utils.isNullEmpty(form.getReserveDto().getFinishYmd()) 
					&& !Utils.isNullEmpty(form.getReserveDto().getFinishTime())){
				reserveStatus = Const.SYSTEM_CONFIG.RESERVE_STATUS_FINISHED;
			} else if (!Utils.isNullEmpty(form.getReserveDto().getInuseYmd()) 
					&& !Utils.isNullEmpty(form.getReserveDto().getInuseTime())){
				reserveStatus = Const.SYSTEM_CONFIG.RESERVE_STATUS_IN_USE;
			} else {
				reserveStatus = Const.SYSTEM_CONFIG.RESERVE_STATUS_BOOKING;
			}
			roomMng.makeUpdateRoomStatus(room.getRoomNo(), reserveStatus, user, conn);
			updRoomState = true;
			
			if (updRoomState){
				count = roomMng.executeUpdate();
				log.info(count + " rows updated.");
			}

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				if (updRoomState){
					roomMng.close();
				}
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * chkExistBill
	 * @param reserveId
	 * @param conn
	 * @return int
	 * @throws ServerException
	 */
	@Override
	public int chkExistBill(String reserveId, Connection conn)
			throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		ResultSet rs = null;
		int count = 0;
		try {
			
			manager.makeChkExistBillSQL(reserveId, conn);
			rs = manager.executeQuery();
			
			if (rs.next()){
				count = rs.getInt("CNT");
			}
			if (count > 0){
				log.info("Bill existed.");
			}else{
				log.info("Bill not exist.");
			}
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
		return count;
	}

	/**
	 * createBill
	 * @param reserveId
	 * @param bill
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void createBill(String reserveId, BillDTO bill, LoginDTO user,
			Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeCreateBillSQL(reserveId, bill, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * deleteBill
	 * @param reserveId
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void deleteBill(String reserveId, LoginDTO user, Connection conn)
			throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeDeleteBillSQL(reserveId, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows deleted.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * updateBill
	 * @param reserveId
	 * @param bill
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void updateBill(String reserveId, BillDTO bill, LoginDTO user,
			Connection conn) throws ServerException {

		AbstractReserveSQLManager manager = SQLFactory.createReserveSQLManager();
		int count = 0;
		try {
			manager.makeUpdateBillSQL(reserveId, bill, user, conn);
			count = manager.executeUpdate();
			log.info(count + " rows updated.");

		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close();

			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
}
